﻿// copyright @ 2009 Farida
//Module : Product Management:To Add,Modify,Delete & Query from Table Product using Stored procedures

using System;
using System.IO;
using System.Data;
using MySql.Data.MySqlClient;

namespace ProjectMain
{

class ClassProd_Mgmt
{
   int userChoice;
   public static void Prod_Mgmt(string username)
    {
        try
        {

            ClassProd_Mgmt prod = new ClassProd_Mgmt();
            //create a MySQL connection with a query string
            MySqlConnection connection = ConnectToDbClass.ConnectDb();
            Console.WriteLine("Server Connected");
            Console.WriteLine("Product Management for {0} ",ClassGlobalVar.GlobalVar);

             do
            {
                prod.userChoice = SubMenuProdClass.SubMenuProd();
                System.Console.WriteLine("You entered {0}", prod.userChoice);


                //Switch (Case statement)

                switch (prod.userChoice)
                {

                    //Insert Data
                    case 1:

                        System.Console.WriteLine("Add Product Id , Product Name,Product Desciption");
                        System.Console.WriteLine("Enter Product Id");

                        int prod_id = System.Convert.ToInt16(Console.ReadLine());

                        System.Console.WriteLine("Product Name");

                        string prod_name = Console.ReadLine();

                        System.Console.WriteLine("Enter Product Description");

                        string prod_descp = Console.ReadLine();



                        System.Console.WriteLine("You entered following values:");

                        System.Console.WriteLine("ProdId :" + prod_id + "Product Name:" + prod_name + " Prod  Description:" + prod_descp);


                        //open Table Connection

                        connection.Open();

                        MySqlCommand cmd = new MySqlCommand("ins_prod", connection);


                        // Set the Command  Object to execute a Stored procedure
                        cmd.CommandType = CommandType.StoredProcedure;

                        //Define Parameters Used
                        MySqlParameter param = new MySqlParameter();

                        param.ParameterName = "?uprod_id";
                        param.Value = prod_id;
                        cmd.Parameters.Add(param);

                        MySqlParameter param1 = new MySqlParameter();
                        param1.ParameterName = "?uprod_name";
                        param1.Value = prod_name;
                        cmd.Parameters.Add(param1);


                        MySqlParameter param2 = new MySqlParameter();

                        param2.ParameterName = "?uprod_descp";
                        param2.Value = prod_descp;
                        cmd.Parameters.Add(param2);


                        MySqlDataReader myReader = cmd.ExecuteReader();

                        while (myReader.Read() != false)
                        {
                            int retValue = myReader.GetInt32(0);
                            Console.WriteLine(retValue);
                        }

                        myReader.Close();
                        connection.Close();
                        break;



                    //Delete Records 
                    case 2:

                        System.Console.WriteLine("Add Product Id , Product Name,Product Desciption");
                        System.Console.WriteLine("Enter Product Id");

                        int delprod_id = System.Convert.ToInt16(Console.ReadLine());

                        System.Console.WriteLine("Product Name");


                        System.Console.WriteLine("You entered following values:");

                        System.Console.WriteLine("ProdId :" + delprod_id);


                        //open Table Connection

                        connection.Open();

                        MySqlCommand delcmd = new MySqlCommand("del_prod", connection);


                        // Set the Command  Object to execute a Stored procedure
                        delcmd.CommandType = CommandType.StoredProcedure;

                        //Define Parameters Used
                        MySqlParameter delparam = new MySqlParameter();

                        delparam.ParameterName = "?uprod_id";
                        delparam.Value = delprod_id;
                        delcmd.Parameters.Add(delparam);


                        MySqlDataReader delmyReader = delcmd.ExecuteReader();

                        while (delmyReader.Read() != false)
                        {
                            int retValue = delmyReader.GetInt32(0);
                            Console.WriteLine(retValue);
                        }

                        delmyReader.Close();
                        connection.Close();
                        
                        break;



                    //Update Records   
                    case 3:

                        System.Console.WriteLine("Update Product Id , Product Name,Product Desciption");
                        System.Console.WriteLine("Enter Product Id to Update ");

                        int updprod_id = System.Convert.ToInt16(Console.ReadLine());

                        System.Console.WriteLine("Enter New Product Name");

                        string updprod_name = Console.ReadLine();

                        System.Console.WriteLine("Enter New Product Description");

                        string updprod_descp = Console.ReadLine();

                        System.Console.WriteLine("You entered following values:");

                        System.Console.WriteLine("ProdId :" + updprod_id + "Product Name:" + updprod_name + " Prod  Description:" + updprod_descp);


                        //open Table Connection

                        connection.Open();

                        MySqlCommand updcmd = new MySqlCommand("upd_prod", connection);


                        // Set the Command  Object to execute a Stored procedure
                        updcmd.CommandType = CommandType.StoredProcedure;

                        //Define Parameters Used
                        MySqlParameter updparam = new MySqlParameter();

                        updparam.ParameterName = "?uprod_id";
                        updparam.Value = updprod_id;
                        updcmd.Parameters.Add(updparam);

                        MySqlParameter updparam1 = new MySqlParameter();
                        updparam1.ParameterName = "?uprod_name";
                        updparam1.Value = updprod_name;
                        updcmd.Parameters.Add(updparam1);


                        MySqlParameter updparam2 = new MySqlParameter();

                        updparam2.ParameterName = "?uprod_descp";
                        updparam2.Value = updprod_descp;
                        updcmd.Parameters.Add(updparam2);


                        MySqlDataReader updmyReader = updcmd.ExecuteReader();

                        while (updmyReader.Read() != false)
                        {
                            int retValue = updmyReader.GetInt32(0);
                            Console.WriteLine(retValue);
                        }

                        updmyReader.Close();
                        connection.Close();
                        break;

                     // Show data
                    case 4:
                       
                          MySqlDataReader rdr =null;
                          //open the connection
                          connection.Open();
                          bool success;
                          try 
                          {
                           //1. Instantiate the command with a query and connection
                           MySqlCommand showcmd = new MySqlCommand("select * from prod",connection);
                     
                           //2. Call Execute Reader to get Query Results 
                          rdr =showcmd.ExecuteReader();
   
                           // print each record 
                          while (rdr.Read())
                         {
                             Console.WriteLine("{0,5} {1,5} {2,5}", rdr[0], rdr[1], rdr[2]);
                         }
                        } 
                        finally 
                        {
                        //close the reader 
                        if (rdr!=null)
                        {
                          rdr.Close();
                        }
                        //close the connection
                       if (connection!=null)
                       {
                          connection.Close();
                       }
                       }
                       Console.ReadLine();
                       break;


                    //Query Data
                    case 5:

                       System.Console.WriteLine("Enter Employee Id");

                       int emp_id = System.Convert.ToInt16(Console.ReadLine());

                       System.Console.WriteLine("You entered following values:");

                       System.Console.WriteLine("EmployeeId :" + emp_id );

                       //open Table Connection

                       connection.Open();

                       MySqlCommand qrycmd = new MySqlCommand("query_prod", connection);


                       // Set the Command  Object to execute a Stored procedure
                       qrycmd.CommandType = CommandType.StoredProcedure;

                       //Define Parameters Used
                       MySqlParameter qryparam = new MySqlParameter();

                       qryparam.ParameterName = "?uemp_id";
                       qryparam.Value = emp_id;
                       qrycmd.Parameters.Add(qryparam);

                       
                       MySqlDataReader qryrdr = qrycmd.ExecuteReader();

                          // print each record 
                        while (qryrdr.Read())
                         {
                             Console.WriteLine("{0,5} {1,5} {2,5} {3,5}", qryrdr[0], qryrdr[1], qryrdr[2],qryrdr[3]);
                         }
                       
                        //close the reader 
                        if (qryrdr!=null)
                        {
                          qryrdr.Close();
                        }
                        //close the connection
                       if (connection!=null)
                       {
                          connection.Close();
                       }
                                   
                       
                       break;

                deafult :
                       Console.WriteLine("{0} is not a valid choice", prod.userChoice);
                       break;

          }

                        
            } while (prod.userChoice != 6);
        }

        catch (Exception excp)
        {
            Exception myExcp = new Exception("Error connecting you to " +
                     "the my sql server. Error: " + excp.Message, excp);
            throw myExcp;

        }
                 
           
        }

    }

}
